# -*- encoding:utf-8 -*-
import json
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from lib.table import table
from config import config
from lib.validform import V, ValidateForm
from lib.record import recodelog
from lib import auth
app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 获取角色权限
@app.route('/api/department/role', methods=['POST'])
@auth.permission("user", "readOnly")
def get_role():
    try:
        form = ValidateForm(role_group_id=['角色功能id', V.optional()], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        role_group_id = data['role_group_id'].replace('\"', '\'')
        g.cursor.execute(
            """select distinct cn_name as value,group_id,father_id from sys_function_group
            where state = '1' and group_id in (%s) """ % (role_group_id))
        rows = g.cursor.fetchall()
        father = []
        childen = []
        son = []
        list1 = []
        for row in rows:
            if row['father_id'] is None:
                father.append(row)
            else:
                childen.append(row)
        for row in father:
            if not row.get('children'):
                row['children'] = []
            for ins in childen:
                if ins['father_id'] == row['group_id']:
                    row['children'].append(ins)
                else:
                    son.append(ins)
                    for i in son:
                        if (i not in list1):
                            list1.append(i)
            for i in row['children']:
                if not i.get('children'):
                    i['children'] = []
                for sons in list1:
                    if i['group_id'] == sons['father_id']:
                        i['children'].append(sons)
        return json.dumps({"status": "success", 'msg': father})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取角色权限信息失败'})


# 获取组织机构关系
@app.route('/api/department/fdepartment', methods=['GET'])
@auth.permission("mechanism", "readOnly")
def fdepartment_name():
    try:
        g.cursor.execute(
            """select agency_id, agency_name as value ,parent_agency_id from sys_agency_info where state = '1'"""
        )
        rows = g.cursor.fetchall()
        first = {}
        second = {}
        for row in rows:
            if row.get("parent_agency_id") is None:
                continue
            if not first.get(row.get("parent_agency_id"), []):
                first[row.get("parent_agency_id")] = []
            first[row.get("parent_agency_id")].append(row.get("agency_id"))
            second[row.get("agency_id")] = row
        for row in rows:
            row['label'] = row['value']
            if row.get("parent_agency_id") is None:
                continue
            for m in first.get(row.get("agency_id", ""), []):
                if not row.get('children'):
                    row["children"] = []
                row["children"].append(second.get(m))
        rows = filter(lambda m: bool(m.get("parent_agency_id") == '0'), rows)
        g.cursor.execute("""select agency_id from sys_agency_info where state='1'""")
        key = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': rows, 'key': key})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败'})


# 获取监管关系
@app.route('/api/department/supervision', methods=['GET'])
@auth.permission("mechanism", "readOnly")
def supervision_name():
    try:
        g.cursor.execute(
            """select agency_id, agency_name as value ,supervision_agency_id from sys_agency_info where state = '1'"""
        )
        rows = g.cursor.fetchall()
        first = {}
        second = {}
        for row in rows:
            if row.get("supervision_agency_id") is None:
                continue
            if not first.get(row.get("supervision_agency_id"), []):
                first[row.get("supervision_agency_id")] = []
            first[row.get("supervision_agency_id")].append(row.get("agency_id"))
            second[row.get("agency_id")] = row
        for row in rows:
            row['label'] = row['value']
            if row.get("supervision_agency_id") is None:
                continue
            for m in first.get(row.get("agency_id", ""), []):
                if not row.get('children'):
                    row["children"] = []
                row["children"].append(second.get(m))
        rows = filter(lambda m: bool(m.get("supervision_agency_id") == '0'), rows)
        g.cursor.execute("""select supervision_agency_id from sys_agency_info where state='1'""")
        key = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': rows, 'key': key})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取监管关系信息失败'})


# 获取机构地区接口
@app.route('/api/mechanism/region', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def region():
    try:
        form = ValidateForm(
            region_name=['子级条件', V.optional()],
            name_region=['父级条件', V.optional()],
            area_id=['子级条件', V.optional()],
            value=['条件值', V.optional()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        if data['region_name'] == 'province_name':
            g.cursor.execute(
                """select distinct %s  from h_geography_info where %s =(select area_id from h_area_info where area_name = '%s') """
                % (data['region_name'], data['name_region'], data['value']))
        elif data['region_name'] == 'city_name' or data['region_name'] == 'district_name':
            g.cursor.execute(
                """select distinct %s  from h_geography_info where %s ='%s' """
                % (data['region_name'], data['name_region'], data['value']))
        else:
            g.cursor.execute("""select area_name,area_id  from h_area_info""")
        rows = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': rows})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败',
            "warn": str(e)
        })


# 获取行业类型
@app.route('/api/mechanism/type', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def get_type():
    try:
        g.cursor.execute("""select type_id,type_name from h_dept_type_info""")
        msg = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': msg})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败'
        })


# 校验部门是否存在
# @app.route('/api/mechanism/jiance', methods=['POST'])
# @auth.permission("mechanism", "readOnly")
# def mechanism_jiance():
#     try:
#         form = ValidateForm(agency_name=['机构名称', V.required()],agency_id=['机构ID', V.required()])
#         (flag, data) = form.validate()
#         if not flag:
#             data['status'] = 'fail'
#             return json.dumps(data)
#         g.cursor.execute(
#             """ select agency_name from sys_agency_info where agency_name = %(agency_name)s and node_type_id = '1' and state='1' and agency_id != %(agency_id)s """,
#             (data))
#         row = g.cursor.fetchone()
#         if row:
#             return json.dumps({'status': 'fail', 'msg': '此部门已存在'})
#         else:
#             return json.dumps({'status': 'success', 'msg': '可以创建'})
#     except Exception, e:
#         return json.dumps({
#             "status": "except",
#             'msg': '获取信息失败'
#         })


# 获取机构详情
@app.route('/api/mechanism/details', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def mechanism_details():
    try:
        form = ValidateForm(aagency_id=['机构编号', V.optional()], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """select a.agency_id as aagency_id,a.agency_name as aagency_name,string_agg(sn.agency_name,',') as department,
               d2t(a.r_time) as ar_time ,a.r_person as ar_person,a.linkman as alinkman,a.telephone as atelephone,a.linkman_email as alinkman_email,b.agency_name as bagency_name,a.remarks as aremarks,a.state as astate,
               b.agency_name as bagency_name,a.belong_area,a.province,a.city,a.area,string_agg(sn.agency_id,',') as sid,e.agency_name as cagency_name,
               c.type_id,c.type_name,b.state as bstate from sys_agency_info as a
               left join (select agency_name,parent_agency_id,agency_id from sys_agency_info where node_type_id = '0') as sn on sn.parent_agency_id = a.agency_id
               left join sys_agency_info as b on b.agency_id = a.parent_agency_id
               left join h_dept_type_info as c on a.type_id = c.type_id
               left join sys_agency_info as e on a.supervision_agency_id = e.agency_id  where a.agency_id = %(aagency_id)s
               group by
               a.agency_id,a.agency_name,e.agency_name,
               d2t(a.r_time),a.r_person,a.linkman,a.telephone,a.linkman_email,b.agency_name,a.remarks,a.state,
               b.agency_name,a.belong_area,a.province,a.city,a.area,
               c.type_id,b.state
                 """, (data))
        msg = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': msg})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败'
        })


# 获取机构信息
@app.route('/api/mechanism/message', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def mechanism_message():
    try:
        tb = table()
        g.cursor.execute(
            """select count(a.agency_id) as num
               from sys_agency_info as a
               left join sys_agency_info as b on b.agency_id = a.parent_agency_id
               left join sys_agency_info as e on a.supervision_agency_id = e.agency_id
               left join h_dept_type_info as c on a.type_id = c.type_id  %s
               """ % (tb.where(['a.agency_name'], "a.agency_id != '0' and a.node_type_id = '1' and a.state = '1'")))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """select a.agency_id as aagency_id,a.agency_name as aagency_name,
               d2t(a.r_time) as ar_time ,a.r_person as ar_person,a.linkman as alinkman,a.telephone as atelephone,a.linkman_email as alinkman_email,b.agency_name as bagency_name,a.remarks as aremarks,a.state as astate,
               b.agency_name as bagency_name,a.belong_area,a.province,a.city,a.area,e.agency_name as sup,
               c.type_name,b.state as bstate
               from sys_agency_info as a
               left join sys_agency_info as b on b.agency_id = a.parent_agency_id
               left join sys_agency_info as e on a.supervision_agency_id = e.agency_id
               left join h_dept_type_info as c on a.type_id = c.type_id  %s
                %s %s %s """ % (tb.where(
                ['a.agency_name'],
                "a.agency_id != '0' and a.node_type_id = '1' and a.state = '1'"), tb.orderBy(), tb.offset(), tb.limit()))
        row = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "msg": {
                "current": tb.current(),
                "total": total,
                "dataSource": row
            }
        })
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '查询信息失败'
        })


# 获取节点信息
@app.route('/api/mechanism/node', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def mechanism_node():
    try:
        form = ValidateForm(
            aagency_id=['机构名称', V.optional()],
            type=['需要类型', V.required()],
            belong_area=['区域名称', V.optional()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        if data['type'] == 'monitoring':
            if data['belong_area']:
                g.cursor.execute(""" select  agency_name,agency_id from sys_agency_info where state = '1' and belong_area = %(belong_area)s""", (data))
            else:
                g.cursor.execute(""" select  agency_name,agency_id from sys_agency_info where state = '1' """)
        else:
            g.cursor.execute(
                """ select agency_name,agency_id from sys_agency_info where agency_id not in (with RECURSIVE node_info as
                    (
                    select agency_id from sys_agency_info where agency_id=%(aagency_id)s
                    union all
                    select s.agency_id from sys_agency_info s inner join node_info n on s.parent_agency_id = n.agency_id
                    )
                    select agency_id from node_info) and node_type_id ='1' and state= '1'""", (data))
        msg = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': msg})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败'
        })


# 获取监管节点信息
@app.route('/api/mechanism/supervision', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def supervision_node():
    try:
        form = ValidateForm(
            aagency_id=['机构名称', V.optional()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """ select agency_name,agency_id from sys_agency_info where agency_id not in (with RECURSIVE node_info as
                    (select agency_id from sys_agency_info where agency_id=%(aagency_id)s union all
                    select s.agency_id from sys_agency_info s inner join node_info n on s.supervision_agency_id = n.agency_id)
                    select agency_id from node_info) and node_type_id ='1' and state= '1'""", (data))
        msg = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': msg})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': '获取信息失败'
        })


# 同步信息数量接口
@app.route('/api/mechanism/snyccount', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def snyccount(_currUser):
    try:
        g.cursor.execute(
            """select count(table_id) from h_synchroniz_info where person_id = '%s' and state ='1' and synchroniz_table ='sys_agency_info' """ % (_currUser['user']['person_id']))
        count = g.cursor.fetchone()
        return json.dumps({"status": "success", "msg": count})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败'})


# 已查看同步信息
@app.route('/api/mechanism/delsnyccount', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def delsnyccount(_currUser):
    try:
        g.cursor.execute(
            """update h_synchroniz_info set state = '0' where person_id = '%s' and synchroniz_table ='sys_agency_info' """ % (_currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success", "msg": '信息删除成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败'})


# 同步信息
@app.route('/api/mechanism/snycmessage', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def snycmessage(_currUser):
    try:
        tb = table()
        _where = """z.synchroniz_table = 'sys_agency_info' and z.person_id = '%s'
                and z.state = '1' and z.operating = '%s'""" % (_currUser["user"]["person_id"], tb.dict['type'])

        g.cursor.execute(
            """ SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_synchroniz_info z 
                left join h_agency_synchroniz_info a on z.r_time = a.r_time
                left join sys_agency_info as x on x.agency_id = a.agency_id %s %s
            """ % (tb.where([], _where), tb.orderBy()))
        row = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "msg": row
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败', "warn": str(e)})


# 历史新增信息
@app.route('/api/mechanism/history_add', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def history_add(_currUser):
    try:
        tb = table()
        _where = """z.synchroniz_table = 'sys_agency_info' and z.person_id = '%s'
                and z.state = '0' and z.operating = 'add'""" % (_currUser["user"]["person_id"])
        g.cursor.execute(
            """SELECT count(1) as num from
            (SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a 
                left join h_synchroniz_info z on z.r_time = a.r_time %s) a
            """ % (tb.where(['a.agency_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a
                left join h_synchroniz_info z on z.r_time = a.r_time %s %s %s %s
            """ % (tb.where(['a.agency_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {        
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败','s':str(e)})


# 历史新增信息
@app.route('/api/mechanism/history_update', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def history_update(_currUser):
    try:
        tb = table()
        _where = """z.synchroniz_table = 'sys_agency_info' and z.person_id = '%s'
                and z.state = '0' and z.operating = 'update'""" % (_currUser["user"]["person_id"])
        g.cursor.execute(
            """SELECT count(1) as num from
            (SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a 
                left join h_synchroniz_info z on z.r_time = a.r_time %s) a
            """ % (tb.where(['a.agency_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a 
                left join h_synchroniz_info z on z.r_time = a.r_time %s %s %s %s
            """ % (tb.where(['a.agency_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {        
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败','s':str(e)})


# 历史新增信息
@app.route('/api/mechanism/history_del', methods=['POST'])
@auth.permission("mechanism", "readOnly")
def history_del(_currUser):
    try:
        tb = table()
        _where = """z.synchroniz_table = 'sys_agency_info' and z.person_id = '%s'
                and z.state = '0' and z.operating = 'delete'""" % (_currUser["user"]["person_id"])
        g.cursor.execute(
            """SELECT count(1) as num from
            (SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a 
                left join h_synchroniz_info z on z.r_time = a.r_time %s) a
            """ % (tb.where(['a.agency_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute(
            """SELECT a.agency_id as aagency_id,a.agency_name_change as aagency_name,d2t(z.r_time) as zr_time,a.person_change as alinkman,a.phoneno_change as atelephone,
                a.email_change as alinkman_email,a.parent_node_change as bagency_name,a.province_change as province,a.city_change as city,a.area_change as area,
                a.supervision_node_change as sup,a.agency_type_change as ctype_name
                from h_agency_synchroniz_info a 
                left join h_synchroniz_info z on z.r_time = a.r_time %s %s %s %s
            """ % (tb.where(['a.agency_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {        
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取信息失败','s':str(e)})

